IF (PMPA IS NULL) THEN SELECT PMPA FROM GET_PMPA(:INDXA) INTO :DPAU;
ELSE DPAU = PMPA;
END
/* DPAU contient le prix d'achat . Soustraire le prix de vente */
END
IF ((REMISE IS NOT NULL) AND (REMISE<>0)) THEN PRIX = PRIX * (100 - REMISE) / 100;
MARGE = (PRIX - DPAU) * QUANTITE;
END
IF (MARGE_L IS NULL OR MARGE_L<>MARGE) THEN UPDATE LIGNES SET MARGE=:MARGE WHERE INDEXPIECE=:INDEX_PIECE AND NOLIGNE=:NO_LIGNE;
SUSPEND;
END
;
ALTER PROCEDURE "GET_MARGE_PIECE"
(
"INDEX_PIECE" INTEGER
)
RETURNS
(
"MARGE" DECIMAL(14, 6)
)
AS
DECLARE VARIABLE NOLIGNE SMALLINT;
DECLARE VARIABLE MARGE1 DECIMAL(14,4);
BEGIN
MARGE = 0;
FOR SELECT NOLIGNE FROM LIGNES WHERE INDEXPIECE=:INDEX_PIECE INTO :NOLIGNE DO
BEGIN
SELECT MARGE FROM GET_MARGE_LIGNE(:INDEX_PIECE,:NOLIGNE) INTO :MARGE1;
IF (MARGE1 IS NOT NULL) THEN MARGE = MARGE + MARGE1;
END
SUSPEND;
END
;
ALTER PROCEDURE "GET_NBR_LIGNES"
(
"INVALUE" INTEGER
)
RETURNS
(
"AVALUE" INTEGER
)
AS
BEGIN
SELECT COUNT(*) FROM LIGNES WHERE INDEXPIECE=:INVALUE INTO :AVALUE;
SUSPEND;
end
;
ALTER PROCEDURE "GET_NOM"
(
"N1" SMALLINT,
"N2" INTEGER
)
RETURNS
(
"AVALUE" VARCHAR(35)
)
AS
BEGIN
IF (n1<13) THEN SELECT UPPER(NOM) FROM CLIENTS WHERE INDX=:N2 INTO AVALUE;
ELSE SELECT UPPER(NOM) FROM FOURNISSEURS WHERE INDX=:N2 INTO AVALUE;
SUSPEND;
END
;
ALTER PROCEDURE "GET_NOM_CLIENT"
(
"INVALUE" INTEGER
)
RETURNS
(
"AVALUE" VARCHAR(85)
)
AS
DECLARE VARIABLE V1 VARCHAR(35);
DECLARE VARIABLE V2 VARCHAR(20);
BEGIN
SELECT C.DESIGNATION, T.PRENOM, T.NOM
FROM CLIENTS T
LEFT JOIN CHAINES C ON (C.TYPEC=7) AND (C.LIGNE=T.CIVILITE-1) AND (C.COLONNE=0)
WHERE T.INDX = :INVALUE
INTO :V1,:V2,AVALUE;
IF (:V2<>'') THEN
BEGIN
AVALUE = V2 || ' ' || AVALUE;
IF ((V1 IS NOT NULL) AND (V2 NOT STARTING WITH (V1 || ' '))) THEN AVALUE = V1 || ' ' || AVALUE;
END
ELSE IF (V1 IS NOT NULL) THEN AVALUE = V1 || ' ' || :AVALUE;
SUSPEND;
END
;
ALTER PROCEDURE "GET_NOM_FOUR"
(
"INVALUE" INTEGER
)
RETURNS
(
"AVALUE" VARCHAR(85)
)
AS
DECLARE VARIABLE V1 VARCHAR(15);
DECLARE VARIABLE V2 VARCHAR(20);
BEGIN
SELECT C.DESIGNATION, T.PRENOM, T.NOM
FROM FOURNISSEURS T
LEFT JOIN CHAINES C ON (C.TYPEC=7) AND (C.LIGNE=T.CIVILITE-1) AND (C.COLONNE=0)
WHERE T.INDX = :INVALUE
INTO :V1,:V2,AVALUE;
IF (:V2<>'') THEN
BEGIN
AVALUE = V2 || ' ' || AVALUE;
IF ((V1 IS NOT NULL) AND (V2 NOT STARTING WITH (V1 || ' '))) THEN AVALUE = V1 || ' ' || AVALUE;
END
ELSE IF (V1 IS NOT NULL) THEN AVALUE = V1 || ' ' || :AVALUE;
SUSPEND;
END
;
ALTER PROCEDURE "GET_NOM_RAYON"
(
"INDX" INTEGER
)
RETURNS
(
"R1" VARCHAR(40),
"R2" VARCHAR(40),
"R3" VARCHAR(40)
)
AS
DECLARE VARIABLE V1 VARCHAR(50);
DECLARE VARIABLE N1 SMALLINT;
DECLARE VARIABLE N2 SMALLINT;
DECLARE VARIABLE N3 SMALLINT;
BEGIN
SELECT RAYON,FAMILLE,SSFAMILLE FROM ARTICLES
WHERE (INDX = :INDX)
INTO N1,N2,N3;
SELECT NOMRAYON FROM RAYONS
WHERE (RY1=:N1) AND (RY2=0) AND (RY3=0)
INTO V1;
IF (V1 IS NULL) THEN
BEGIN
R1 = '?';
END
ELSE R1 = V1;
SELECT NOMRAYON FROM RAYONS
WHERE (RY1=:N1) AND (RY2=:N2) AND (RY3=0)
INTO V1;
IF (V1 IS NULL) THEN
BEGIN
R2 = '?';
END
ELSE R2 = V1;
SELECT NOMRAYON FROM RAYONS
WHERE (RY1=:N1) AND (RY2=:N2) AND (RY3=:N3)
INTO V1;
IF (V1 IS NULL) THEN
BEGIN
R3 = '?';
END
ELSE R3 = V1;
SUSPEND;
END
;
ALTER PROCEDURE "GET_NOM_TIERS"
(
"N1" SMALLINT,
"N2" INTEGER
)
RETURNS
(
"NOM_COMPLET" VARCHAR(200)
)
AS
DECLARE VARIABLE V1 VARCHAR(35);
DECLARE VARIABLE V2 VARCHAR(35);
DECLARE VARIABLE NOM VARCHAR(35);
DECLARE VARIABLE C SMALLINT;
BEGIN
IF (n1<13) THEN SELECT CIVILITE, PRENOM, NOM FROM CLIENTS WHERE INDX=:N2 INTO :C,:V2,NOM;
ELSE SELECT CIVILITE, PRENOM, NOM FROM FOURNISSEURS WHERE INDX=:N2 INTO :C,:V2,NOM;
SELECT DESIGNATION FROM CHAINES WHERE TYPEC=7 AND LIGNE=:C-1 AND COLONNE=0 INTO :V1;
IF (:V2<>'') THEN
BEGIN
NOM_COMPLET = V2 || ' ' || NOM;
IF ((V1 IS NOT NULL) AND (V2 NOT STARTING WITH (V1 || ' '))) THEN NOM_COMPLET = V1 || ' ' || NOM_COMPLET;
END
ELSE IF (V1 IS NOT NULL) THEN
BEGIN
NOM_COMPLET = V1 || ' ' || NOM;
END
ELSE NOM_COMPLET = NOM;
SUSPEND;
END
;
ALTER PROCEDURE "GET_PMPA"
(
"INDX" INTEGER
)
RETURNS
(
"PMPA" DECIMAL(12, 2)
)
AS
DECLARE VARIABLE PRIX DECIMAL(14,6);
DECLARE VARIABLE QUANTITE DECIMAL(11,2);
DECLARE VARIABLE REMISE DECIMAL(5,2);
DECLARE VARIABLE OLD_PMPA DECIMAL(12,2);
DECLARE VARIABLE NOMBRE DECIMAL(14,6);
DECLARE VARIABLE CDA DECIMAL(11,2);
DECLARE VARIABLE TOTALPA DECIMAL(14,4);
BEGIN
TOTALPA=0;
NOMBRE=0;
/* Selectionner factures achat */
FOR SELECT PRIX,QUANTITE,REMISE FROM LIGNES L
JOIN PIECES P ON P.INDX=l.INDEXPIECE AND TYPEPIECE=14
WHERE INDEXARTICLE=:INDX
AND QUANTITE IS NOT NULL
AND QUANTITE>0
INTO :PRIX,:QUANTITE,:REMISE DO
BEGIN
SELECT MAX(LOT) FROM ARTFOUR WHERE DPA=:PRIX AND INDXART=:INDX INTO CDA;
IF ((CDA IS NOT NULL) AND (CDA>0)) THEN PRIX = PRIX / CDA;
IF ((REMISE IS NOT NULL) AND (REMISE<>0)) THEN PRIX = PRIX * (100 - REMISE) / 100;
TOTALPA = TOTALPA + (PRIX * QUANTITE);
NOMBRE = NOMBRE + QUANTITE;
END
IF (NOMBRE=0) THEN SELECT DPA FROM GET_LAST_DPA(:INDX) INTO :PMPA;
ELSE PMPA = TOTALPA / NOMBRE;
SELECT PMPA FROM ARTICLES WHERE INDX=:INDX INTO :OLD_PMPA;
IF ((OLD_PMPA IS NULL) OR (PMPA<>OLD_PMPA)) THEN UPDATE ARTICLES SET PMPA=:PMPA WHERE INDX=:INDX;
SUSPEND;
END
;
ALTER PROCEDURE "GET_PURGE"
(
"TYPEPURGE" SMALLINT,
"RCH_TEMP" SMALLINT
)
RETURNS
(
"INDXOUT" INTEGER,
"DESI" VARCHAR(60)
)
AS
BEGIN
IF (TYPEPURGE=1) THEN
FOR SELECT INDX,NOM||' '||PRENOM FROM CLIENTS WHERE TEMP=:RCH_TEMP ORDER BY NOM INTO :INDXOUT,:DESI DO SUSPEND;
ELSE IF (TYPEPURGE=2) THEN
FOR SELECT INDX,NOM||' '||PRENOM FROM FOURNISSEURS WHERE TEMP=:RCH_TEMP ORDER BY NOM INTO :INDXOUT,:DESI DO SUSPEND;
ELSE IF (TYPEPURGE=3) THEN
FOR SELECT INDX,D.TEXTE FROM ARTICLES A LEFT JOIN DESIGNATIONS D ON D.INDXART=A.INDX AND CODELANGUE=1 WHERE TEMP=:RCH_TEMP ORDER BY 2 INTO :INDXOUT,:DESI DO SUSPEND;
END
;
ALTER PROCEDURE "GET_R123"
(
"INDX" INTEGER
)
RETURNS
(
"R1" VARCHAR(40),
"R2" VARCHAR(40),
"R3" VARCHAR(40)
)
AS
DECLARE VARIABLE V1 VARCHAR(50);
DECLARE VARIABLE N1 SMALLINT;
DECLARE VARIABLE N2 SMALLINT;
DECLARE VARIABLE N3 SMALLINT;
BEGIN
SELECT RY1,RY2,RY3 FROM RAYONS WHERE PKEY = :INDX INTO :N1,:N2,:N3;
SELECT NOMRAYON FROM RAYONS WHERE (RY1=:N1) AND (RY2=0) AND (RY3=0) INTO :R1;
if (n2<>0) then
begin
SELECT NOMRAYON FROM RAYONS WHERE (RY1=:N1) AND (RY2=:N2) AND (RY3=0) INTO :R2;
end
else
begin
R2 = '';
end
if (n3<>0) then
begin
SELECT NOMRAYON FROM RAYONS WHERE (RY1=:N1) AND (RY2=:N2) AND (RY3=:N3) INTO :R3;
end
else
begin
R3 = '';
end
SUSPEND;
END
;
ALTER PROCEDURE "GET_RAYON_LIBRE"
RETURNS
(
"RY" SMALLINT
)
AS
DECLARE VARIABLE I INTEGER;
BEGIN
RY = 1;
WHILE (RY<99) DO
BEGIN
SELECT COUNT(*) FROM RAYONS WHERE RY1=:RY INTO I;
IF (I>0) THEN RY = RY + 1;
ELSE
BEGIN
UPDATE ARTICLES SET RAYON=0,FAMILLE=0,SSFAMILLE=0 WHERE RAYON=:RY;
EXIT;
END
END
END
;
ALTER PROCEDURE "GET_REMISE_CLIENTS"
(
"DATEDEB" DATE,
"DATEFIN" DATE
)
RETURNS
(
"INDEX_CLIENT" INTEGER,
"CA" DECIMAL(14, 2),
"REMISE" DECIMAL(5, 2),
"CA_TOTAL" DECIMAL(14, 2)
)
AS
BEGIN
FOR SELECT INDX FROM CLIENTS INTO :INDEX_CLIENT DO
BEGIN
SELECT "CA","CA_TOTAL" FROM GET_CA_CLIENT(:INDEX_CLIENT,:DATEDEB,:DATEFIN) INTO CA,CA_TOTAL;
IF (CA IS NULL) THEN
BEGIN
REMISE=0;
CA = 0;
END
ELSE SELECT "REMISE" FROM CALCUL_REMISE(:CA) INTO :REMISE;
SUSPEND;
END
END
;
ALTER PROCEDURE "GET_SFAMILLE_LIBRE"
(
"RY1" SMALLINT,
"RY2" SMALLINT
)
RETURNS
(
"RY3" SMALLINT
)
AS
DECLARE VARIABLE I INTEGER;
BEGIN
RY3 = 1;
WHILE (RY3<99) DO
BEGIN
SELECT COUNT(*) FROM RAYONS WHERE RY1=:RY1 AND RY2=:RY2 AND RY3=:RY3 INTO I;
IF (I>0) THEN RY3 = RY3 + 1;
ELSE
BEGIN
UPDATE ARTICLES SET SSFAMILLE=0 WHERE RAYON=:RY1 AND FAMILLE=:RY2 AND SSFAMILLE=:RY3;
EXIT;
END
END
END
;
ALTER PROCEDURE "GET_TITREPC"
(
"INVALUE" INTEGER
)
RETURNS
(
"AVALUE" VARCHAR(20)
)
AS
BEGIN
SELECT c.designation
from CHAINES C
WHERE (c.TYPEC=0)AND (c.LIGNE=:INVALUE) AND (C.COLONNE=0)
INTO AVALUE;
SUSPEND;
end
;
ALTER PROCEDURE "INTERROGATION_PONTA"
AS
BEGIN EXIT; END
;
ALTER PROCEDURE "INTERROGATION_PONTV"
AS
BEGIN EXIT; END
;
ALTER PROCEDURE "IV_ADD"
(
"NOCAISSE" SMALLINT
)
AS
DECLARE VARIABLE I INTEGER;
DECLARE VARIABLE Q DECIMAL(6,2);
DECLARE VARIABLE D DECIMAL(6,2);
DECLARE VARIABLE IV DECIMAL(6,2);
BEGIN
FOR SELECT INDXART,SUM(Q1) AS Q1 FROM LISTE WHERE NOCAISSE=:NOCAISSE GROUP BY INDXART INTO :I,:Q DO
BEGIN
SELECT DISPO,INVENTAIRE FROM ARTICLES WHERE INDX=:I INTO :D,:IV;
IF (:IV IS NULL) THEN IV=Q-D; ELSE IV=Q+IV;
UPDATE ARTICLES SET INVENTAIRE=:IV,DATEINVEN='NOW' WHERE INDX=:I;
END
END
;
ALTER PROCEDURE "MAJ_AVOIR"
(
"INDEX_CLIENT" INTEGER
)
RETURNS
(
"N_AVOIR" DECIMAL(14, 2)
)
AS
DECLARE VARIABLE AV1 DECIMAL(14,2);
DECLARE VARIABLE AV2 DECIMAL(14,2);
BEGIN
SELECT SUM(SOLDE) FROM PIECES WHERE SOLDE<0 AND Z=1 AND NOTIERS=:INDEX_CLIENT INTO AV1;
SELECT SUM(MONTANT) from VERSEMENTS V JOIN PIECES P ON V.TYPEV=3 AND V.INDEXPIECE=P.INDX AND P.Z=1 AND NOTIERS=:INDEX_CLIENT INTO :AV2;
N_AVOIR = -:AV1 - :AV2;
SELECT AVOIR FROM CLIENTS WHERE INDX=:INDEX_CLIENT INTO :AV1;
IF (AV1<>N_AVOIR) THEN UPDATE CLIENTS SET AVOIR=:N_AVOIR WHERE INDX=:INDEX_CLIENT;
SUSPEND;
end
;
ALTER PROCEDURE "MAJ_PURGE"
(
"INDEXP" INTEGER
)
AS
DECLARE VARIABLE V1 SMALLINT;
DECLARE VARIABLE I INTEGER;
BEGIN
SELECT TYPEPIECE,NOTIERS FROM PIECES WHERE INDX=:INDEXP INTO :V1,:I;
IF (:I>0) THEN
BEGIN
IF (:V1<13) THEN
UPDATE CLIENTS SET TEMP=1 WHERE INDX=:I AND TEMP<>1;
ELSE
UPDATE FOURNISSEURS SET TEMP=1 WHERE INDX=:I AND TEMP<>1;
END
FOR SELECT INDEXARTICLE FROM LIGNES WHERE INDEXPIECE=:INDEXP
AND INDEXARTICLE IS NOT NULL AND INDEXARTICLE>0
GROUP BY INDEXARTICLE
INTO :I
DO UPDATE ARTICLES SET TEMP=1 WHERE INDX=:I AND TEMP<>1;
END
;
ALTER PROCEDURE "MAJ_STOCK_ARTICLE"
(
"INDEXA" INTEGER,
"Q" DECIMAL(11, 2),
"TYPEMVT" SMALLINT
)
AS
DECLARE VARIABLE S SMALLINT;
BEGIN
SELECT SUIVI FROM ARTICLES WHERE INDX=:INDEXA INTO :S;
IF (S<>0) THEN
BEGIN
IF (TYPEMVT=4) THEN TYPEMVT=0;
/* TICKET, FACTURE */
IF (TYPEMVT=0) THEN UPDATE ARTICLES SET DISPO=DISPO-:Q WHERE INDX=:INDEXA;
/* RESERVATION */
ELSE IF (TYPEMVT=2) THEN UPDATE ARTICLES SET DISPO=DISPO-:Q,RESERVE=RESERVE+:Q WHERE INDX=:INDEXA;
/* BL */
ELSE IF (TYPEMVT=3) THEN UPDATE ARTICLES SET DISPO=DISPO-:Q,BL_CLIENT=BL_CLIENT+:Q WHERE INDX=:INDEXA;
/* PRET */
ELSE IF (TYPEMVT=5) THEN UPDATE ARTICLES SET DISPO=DISPO-:Q,EN_PRET=EN_PRET+:Q WHERE INDX=:INDEXA;
/* SAV */
ELSE IF (TYPEMVT=6) THEN UPDATE ARTICLES SET EN_SAV=EN_SAV+:Q WHERE INDX=:INDEXA;
/* RETOUR SAV */
ELSE IF (TYPEMVT=7) THEN UPDATE ARTICLES SET EN_SAV=EN_SAV-:Q WHERE INDX=:INDEXA;
/* AVOIR */
ELSE IF (TYPEMVT=8) THEN UPDATE ARTICLES SET DISPO=DISPO+:Q WHERE INDX=:INDEXA;
/* COMMANDE */
ELSE IF (TYPEMVT=13) THEN UPDATE ARTICLES SET EN_COMMANDE=EN_COMMANDE+:Q WHERE INDX=:INDEXA;
/* LIVRAISON */
ELSE IF (TYPEMVT=14) THEN UPDATE ARTICLES SET DISPO=DISPO+:Q WHERE INDX=:INDEXA;
END
END
;
ALTER PROCEDURE "MAJ_STOCK_PIECE"
(
"INDEXP" INTEGER,
"DELTA" SMALLINT
)
AS
DECLARE VARIABLE V1 SMALLINT;
DECLARE VARIABLE I INTEGER;
DECLARE VARIABLE IB INTEGER;
DECLARE VARIABLE Q DECIMAL(11,2);
DECLARE VARIABLE Q1 DECIMAL(11,2);
BEGIN
/* DΘterminer ce que la piΦce doit faire comme type de mouvement de stock */
SELECT TYPESTOCK FROM PIECES WHERE INDX=:INDEXP AND TYPESTOCK<>1 AND STATUT IN (0,3) INTO V1;
IF (V1 IS NOT NULL) THEN
BEGIN
FOR SELECT INDEXARTICLE,SUM(QUANTITE),SUM(QTE_LIVRE) FROM LIGNES
WHERE INDEXPIECE=:INDEXP
AND INDEXARTICLE IS NOT NULL AND INDEXARTICLE>0
AND QUANTITE IS NOT NULL AND QUANTITE<>0
GROUP BY INDEXARTICLE
INTO :I,:Q,:Q1
DO
BEGIN
IF (Q IS NULL) THEN Q=0;
IF (Q1 IS NOT NULL) THEN Q=Q-Q1;
Q =Q * :DELTA;
IF (Q<>0) THEN
BEGIN
EXECUTE PROCEDURE MAJ_STOCK_ARTICLE(:I,:Q,:V1);
/* Faire les bundles */
FOR SELECT INDX_2,QTE FROM BUNDLE WHERE INDX_1=:I INTO :IB,:Q1 DO
UPDATE CLIENTS SET PRENOM='' WHERE PRENOM IS NULL;
UPDATE FOURNISSEURS SET PRENOM='' WHERE PRENOM IS NULL;
END
;
ALTER PROCEDURE "SET_LIVRE"
(
"INDXPIECE" INTEGER
)
AS
DECLARE VARIABLE N INTEGER;
DECLARE VARIABLE OLD_L SMALLINT;
DECLARE VARIABLE NEW_L SMALLINT;
BEGIN
SELECT TYPEPIECE,LIVRE FROM PIECES WHERE INDX=:INDXPIECE INTO :N,:OLD_L;
IF (N IN (1,2,3,13)) THEN /* TESTER SI QTE */
begin
SELECT COUNT(*) FROM LIGNES
WHERE INDEXPIECE=:INDXPIECE AND (QUANTITE IS NULL OR QTE_LIVRE IS NULL OR QUANTITE<>QTE_LIVRE)
INTO :N;
/* N=0 donc pas de lignes ou la quantitΘ livrΘ diffΦre de la quantitΘ initiale, donc piΦce livrΘe */
IF (N=0) THEN NEW_L=1; ELSE NEW_L=0;
END
ELSE NEW_L=NULL;
IF (OLD_L IS NULL AND NEW_L IS NULL) THEN EXIT;
IF (OLD_L IS NULL OR NEW_L IS NULL OR (OLD_L<>NEW_L)) THEN UPDATE PIECES SET LIVRE=:NEW_L WHERE INDX=:INDXPIECE;
end
;
ALTER PROCEDURE "SET_NBR_LIGNES"
(
"INDXPIECE" INTEGER,
"DELP" SMALLINT
)
AS
DECLARE VARIABLE N INTEGER;
BEGIN
SELECT COUNT(*) FROM LIGNES WHERE INDEXPIECE=:INDXPIECE INTO :N;
IF ((DELP<>0) AND (N=0)) THEN DELETE FROM PIECES WHERE INDX=:INDXPIECE;
ELSE UPDATE PIECES SET NBR_LIGNES=:N WHERE INDX=:INDXPIECE;
END
;
ALTER PROCEDURE "SET_VERROU"
(
"INDXPIECE" INTEGER,
"PLIVRE" SMALLINT,
"REGROUPE" SMALLINT,
"PAYE" SMALLINT
)
AS
DECLARE VARIABLE N INTEGER;
DECLARE VARIABLE V_Z SMALLINT;
DECLARE VARIABLE V_SOLDE DECIMAL(14,4);
DECLARE VARIABLE V_LIVRE SMALLINT;
DECLARE VARIABLE V_GROUP INTEGER;
DECLARE VARIABLE V_STAT SMALLINT;
DECLARE VARIABLE NEW_STAT SMALLINT;
BEGIN
SELECT Z,SOLDE,LIVRE,REGROUPE_DANS,STATUT FROM PIECES WHERE INDX=:INDXPIECE
INTO :V_Z,:V_SOLDE,:V_LIVRE,V_GROUP,V_STAT;
/* SEUL LES PIECES NORMALES (STATUT=0) OU ARCHIVEES (STATUT=1) SONT A FAIRE */
IF (V_STAT>1) THEN EXIT;
NEW_STAT=0;
/* CONDITION DE VERROUILLAGE */
IF (
(:PAYE=1 AND V_Z=1 AND V_SOLDE=0)
OR
(:PLIVRE=1 AND V_LIVRE=1)
OR
(REGROUPE=1 AND V_GROUP>0)
) THEN
BEGIN
NEW_STAT=1;
/*CONDITION DE DEVERROUILLAGE */
IF (
(:PAYE=1 AND V_Z=1 AND V_SOLDE<>0)
OR
(:PLIVRE=1 AND V_LIVRE=0)
) THEN NEW_STAT=0;
END
IF (NEW_STAT<>V_STAT) THEN UPDATE PIECES SET STATUT=:NEW_STAT WHERE INDX=:INDXPIECE;
EXIT;
end
;
ALTER PROCEDURE "UPDATE_REMISE_CLIENTS"
(
"DATEDEB" DATE,
"DATEFIN" DATE
)
AS
DECLARE VARIABLE "INDEX_CLIENT" INTEGER;
DECLARE VARIABLE "CA" DECIMAL(14, 2);
DECLARE VARIABLE "REMISE" DECIMAL(5, 2);
BEGIN
FOR SELECT INDX FROM CLIENTS INTO :INDEX_CLIENT DO
BEGIN
SELECT "CA" FROM GET_CA_CLIENT(:INDEX_CLIENT,:DATEDEB,:DATEFIN) INTO :CA;
IF (CA IS NULL) THEN REMISE=0;
ELSE SELECT "REMISE" FROM CALCUL_REMISE(:CA) INTO :REMISE;
UPDATE CLIENTS SET "REMISE"=:REMISE WHERE "INDX"=:INDEX_CLIENT;
END
END
;
ALTER PROCEDURE "VALEUR_STOCK"
RETURNS
(
"S_INDEX" INTEGER,
"S_RAYON" SMALLINT,
"S_DISPO" DECIMAL(12, 2),
"S_INVENTAIRE" DECIMAL(12, 2)
)
AS
DECLARE variable P1 DECIMAL(12,2);
DECLARE variable Q1 DECIMAL(6,2);
DECLARE variable Q2 DECIMAL(6,2);
DECLARE VARIABLE N1 INTEGER;
DECLARE VARIABLE N2 INTEGER;
BEGIN
For SELECT INDX,RAYON,PMPA,DISPO,INVENTAIRE,DERFOUR FROM ARTICLES
WHERE SUIVI<>0
INTO :S_INDEX,S_RAYON,:P1,:Q1,:Q2,:N1 do
begin
IF (P1 IS NULL) THEN
BEGIN
SELECT MIN(DPA) FROM ARTFOUR WHERE INDXART=:S_INDEX AND CODEFOUR=:N1 INTO :P1;
IF (P1 IS NULL) THEN
BEGIN
SELECT MIN(DPA) FROM ARTFOUR WHERE INDXART=:S_INDEX INTO :P1;
END
END
S_DISPO = P1 * Q1;
IF (Q2 IS NULL) THEN S_INVENTAIRE = S_DISPO; ELSE S_INVENTAIRE = P1 * (Q1+Q2);
SUSPEND;
end
END
;
COMMIT WORK;
SET AUTODDL ON;
/* Triggers only will work for SQL triggers */
CREATE TRIGGER "SETPK_ADRESSE" FOR "ADRESSES"
ACTIVE BEFORE INSERT POSITION 0
as
begin
if (new."INDX" IS NULL) THEN
new."INDX" = GEN_ID(GENPK_ADRESSE,1);
end
;
CREATE TRIGGER "SETPK_ARTFOUR" FOR "ARTFOUR"
ACTIVE BEFORE INSERT POSITION 0
as
begin
if (new."INDEXAF" IS NULL) THEN
new."INDEXAF" = GEN_ID(GENPK_ARTFOUR,1);
end
;
CREATE TRIGGER "SETPK_ART" FOR "ARTICLES"
ACTIVE BEFORE INSERT POSITION 0
as
begin
if (new."INDX" IS NULL) THEN
new."INDX" = GEN_ID(GENPK_ART,1);
end
;
CREATE TRIGGER "ADD_DISPO" FOR "ARTICLES"
INACTIVE AFTER INSERT POSITION 0
as
begin
INSERT INTO HISTO_DISPO (INDX,"USER",DATEMODIF,NEW_DISPO,DELTA) VALUES (new."INDX",USER,'NOW',NEW."DISPO",NEW."DISPO");
INSERT INTO HISTO_INVEN (INDX,"USER",DATEMODIF,NEW_INVEN,DELTA) VALUES (new."INDX",USER,'NOW',NEW."INVENTAIRE",NEW."INVENTAIRE");
end
;
CREATE TRIGGER "SET_DATEMODIF_A" FOR "ARTICLES"
ACTIVE BEFORE UPDATE POSITION 0
as
begin
new."DATEMODIF" = 'NOW';
end
;
CREATE TRIGGER "ADD_DISPO1" FOR "ARTICLES"
INACTIVE AFTER UPDATE POSITION 0
as
begin
if (old."DISPO" IS NULL AND new."DISPO" IS NOT NULL) THEN INSERT INTO HISTO_DISPO (INDX,"USER",DATEMODIF,ANC_DISPO,NEW_DISPO,DELTA) VALUES (new."INDX",USER,'NOW',old."DISPO",NEW."DISPO",NEW."DISPO");
ELSE
begin
if (old."DISPO" IS NOT NULL AND new."DISPO" IS NULL) THEN INSERT INTO HISTO_DISPO (INDX,"USER",DATEMODIF,ANC_DISPO,NEW_DISPO,DELTA) VALUES (new."INDX",USER,'NOW',old."DISPO",NEW."DISPO",-OLD."DISPO");
else
begin
if (new."DISPO"<>old."DISPO") THEN INSERT INTO HISTO_DISPO (INDX,"USER",DATEMODIF,ANC_DISPO,NEW_DISPO,DELTA) VALUES (new."INDX",USER,'NOW',old."DISPO",NEW."DISPO",NEW."DISPO"-OLD."DISPO");
end
end
if (old."INVENTAIRE" IS NULL AND new."INVENTAIRE" IS NOT NULL) THEN INSERT INTO HISTO_INVEN (INDX,"USER",DATEMODIF,ANC_INVEN,NEW_INVEN,DELTA) VALUES (new."INDX",USER,'NOW',old."INVENTAIRE",NEW."INVENTAIRE",NEW."INVENTAIRE");
ELSE
begin
if (old."INVENTAIRE" IS NOT NULL AND new."INVENTAIRE" IS NULL) THEN INSERT INTO HISTO_INVEN (INDX,"USER",DATEMODIF,ANC_INVEN,NEW_INVEN,DELTA) VALUES (new."INDX",USER,'NOW',old."INVENTAIRE",NEW."INVENTAIRE",-OLD."INVENTAIRE");
else
begin
if (new."INVENTAIRE"<>old."INVENTAIRE") THEN INSERT INTO HISTO_INVEN (INDX,"USER",DATEMODIF,ANC_INVEN,NEW_INVEN,DELTA) VALUES (new."INDX",USER,'NOW',old."INVENTAIRE",NEW."INVENTAIRE",NEW."INVENTAIRE"-OLD."INVENTAIRE");
end
end
end
;
CREATE TRIGGER "SETPK_BLOB" FOR "BLOBS"
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW."INDX" IS NULL) THEN
NEW."INDX" = GEN_ID(GENPK_BLOBS,1);
END
;
CREATE TRIGGER "BLOB_CHANGE" FOR "BLOBS"
ACTIVE AFTER UPDATE POSITION 0
AS
BEGIN
IF (new.indx=1) THEN POST_EVENT 'MODIF_BLOB1';
END
;
CREATE TRIGGER "SETPK_CLIENT" FOR "CLIENTS"
ACTIVE BEFORE INSERT POSITION 0
as
begin
if (new."INDX" IS NULL) THEN
new."INDX" = GEN_ID(GENPK_CLIENT,1);
end
;
CREATE TRIGGER "SET_DATEMODIF" FOR "CLIENTS"
ACTIVE BEFORE UPDATE POSITION 0
as
begin
new."DATEMODIF" = 'NOW';
end
;
CREATE TRIGGER "SETPK_ETAT" FOR "ETATS"
ACTIVE BEFORE INSERT POSITION 0
as
begin
if (new."INDX" IS NULL) THEN
new."INDX" = GEN_ID(GENPK_ETATS,1);
end
;
CREATE TRIGGER "SETPK_FOUR" FOR "FOURNISSEURS"
ACTIVE BEFORE INSERT POSITION 0
as
begin
if (new."INDX" IS NULL) THEN
new."INDX" = GEN_ID(GENPK_FOUR,1);
end
;
CREATE TRIGGER "SET_DATEMODIF_F" FOR "FOURNISSEURS"
